package pyip.lib.javalearn.jdbc.batch;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;

import org.apache.log4j.Logger;

public class RemoteAreaDAO {
    private static final Logger log = Logger.getLogger(RemoteAreaDAO.class);

    private static final int BATCH_SIZE = 100;

    // TODO
    public Connection getConnection() throws SQLException {
        return null;
    }

    public void addRemoteArea(List<RemoteArea> remoteAreas) throws SQLException {
        String sql = "insert into DHL_REMOTEAREA_TEMP (COUNTRY, CT_CODE, RA_CITYNAME, RA_POSTCODE, RA_CREATEDATE) values (?,?,?,?,?)";
        Connection connection = null;
        PreparedStatement pstmt = null;

        boolean oldAutoCommit = false;
        try {
            connection = getConnection();
            oldAutoCommit = connection.getAutoCommit();
            connection.setAutoCommit(false);

            pstmt = connection.prepareStatement(sql);

            int total = 0;
            for (RemoteArea remoteArea : remoteAreas) {
                pstmt.setString(1, remoteArea.getCountry());
                pstmt.setString(2, remoteArea.getCountryCode());
                pstmt.setString(3, remoteArea.getCity());
                pstmt.setString(4, remoteArea.getPostalCode());
                long timeMillis = (null == remoteArea.getCreateDate() ? System.currentTimeMillis() : remoteArea.getCreateDate().getTime());
                pstmt.setTimestamp(5, new Timestamp(timeMillis));
                // 必须添加到batch, 否则调用executeBatch是没有效果的, 不过报错, 也不会保存
                pstmt.addBatch();

                if (++total % BATCH_SIZE == 0) {
                    pstmt.executeBatch();
                    // pstmt.clearBatch();
                }
            }

            pstmt.executeBatch();
            connection.commit();
        } catch (SQLException e) {
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException e1) {
                    log.error(e1);
                }
            }

            throw e;
        } finally {
            connection.setAutoCommit(oldAutoCommit);
            if (null != connection) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    log.error(e);
                }
            }
            if (null != pstmt) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    log.error(e);
                }
            }
        }
    }
}
